package convert

import (
	"crypto/md5"
	"database/sql"
	"errors"
	"fmt"
	"gitee.com/cajan2/xlsxtodb/pkg/set"
	"gitee.com/cajan2/xlsxtodb/pkg/utils"
	"github.com/google/uuid"
	"github.com/lithammer/shortuuid"
	"github.com/tealeg/xlsx"
	"golang.org/x/crypto/bcrypt"
	"strconv"
	"strings"
)

func FromExcelSheet(c *Columns, sheet *xlsx.Sheet, db *sql.DB, dataStartRow int, driverName, tableName string,
	sheetIndex string, debug bool, useNull4BlankColumn bool) (modifiedIds []int, resultValues []string, err error) {
	return FromExcelSheetWithResult(c, sheet, db, dataStartRow, driverName, tableName,
		sheetIndex, debug, useNull4BlankColumn, "")
}

//执行附表操作
func operateOnOtherTable(driverName string, db *sql.DB, dbRow *DBRow, idOfMainRecord int,
	needConflictOnFieldOtherSet string, debug bool) {
	insertSql := ""
	updateSetSql := ""
	whereSql := ""
	distinctExcludedFieldSet := set.New()
	updatedFieldSet := set.New()
	tableNameOther := ""
	var tmp int
	if dbRow.ot.value != nil {
		tableNameOther = dbRow.ot.value[0]
		tmp = 0
		var fieldNames []string
		var values []string
		for key, value := range dbRow.ot.columns {
			var pro bool
			dbRow.ot.value[key+1], pro = ParseValue(dbRow.ot.value[key+1])
			if dbRow.ot.value[key+1] == ":id" {
				dbRow.ot.value[key+1] = strconv.Itoa(idOfMainRecord)
			}
			if dbRow.ot.value[key+1] != "" {
				if value != "" {
					fieldNames = append(fieldNames, value)
					values = append(values, dbRow.ot.value[key+1])
					updatedFieldSet.Add(value)
					if !pro {
						distinctExcludedFieldSet.Add(value)
					}
					tmp++
				}
			}
		}
		insertSql, updateSetSql, whereSql = GetUpdateSql(driverName, tableNameOther, fieldNames, values,
			needConflictOnFieldOtherSet, updatedFieldSet, distinctExcludedFieldSet)
		dbRow.ot.sql = insertSql
		if needConflictOnFieldOtherSet != "" {
			if updateSetSql != "" {
				dbRow.ot.sql += " ON CONFLICT (" + needConflictOnFieldOtherSet + ") DO UPDATE SET " +
					updateSetSql + whereSql
			}
		}
		if debug {
			fmt.Printf("dbRow.ot.sql:%s\n", dbRow.ot.sql)
		}
		rows, err := db.Query(dbRow.ot.sql + ";")
		if rows != nil {
			defer rows.Close()
		}
		utils.Checkerr2(err, dbRow.ot.sql)
	}
}

func generateUniqTogetherSql(driverName string, tableName string, uniqTogetherMap map[string]string,
	usezero bool) (uniqTogetherSql string) {
	uniqTogetherSql = " select * from " + tableName + " where "
	indexTemp := 0
	for field, value := range uniqTogetherMap {
		if indexTemp > 0 {
			uniqTogetherSql += " and "
		}
		if value != "NULL" {
			uniqTogetherSql += field + "=" + utils.EscapeValuesString(driverName, value)
		} else {
			if usezero {
				uniqTogetherSql += field + "=0"
			} else {
				uniqTogetherSql += field + " is NULL"
			}
		}
		indexTemp += 1
	}
	return
}

func XlFileToDB(db *sql.DB, driverName, tableName string, xlFile *xlsx.File, sheets string, dataStartRow int,
	debug bool, useNull4BlankColumn bool) (resultValues []ResultStruct, err error) {
	return XlFileToDBWithResult(db, driverName, tableName, xlFile, sheets,
		dataStartRow, debug, useNull4BlankColumn, "")
}

func ExcelToDB(db *sql.DB, driverName, tableName, excelFileName, sheets string, dataStartRow int, debug bool,
	useNull4BlankColumn bool) (resultValues []ResultStruct, err error) {
	var xlFile *xlsx.File
	xlFile, err = xlsx.OpenFile(excelFileName)
	if err != nil {
		utils.Checkerr2(err, excelFileName)
		return
	}
	return XlFileToDB(db, driverName, tableName, xlFile, sheets, dataStartRow, debug, useNull4BlankColumn)
}

func XlFileToDBWithResult(db *sql.DB, driverName, tableName string, xlFile *xlsx.File, sheets string, dataStartRow int,
	debug bool, useNull4BlankColumn bool, resultFieldName string) (resultValues []ResultStruct, err error) {
	sheetSlice := strings.Split(sheets, ",")
	sign := make(chan string, len(sheetSlice))
	resultChan := make(chan ResultStruct, len(sheetSlice))
	for _, sheetIndex := range sheetSlice {
		go func(sheetIndex string) {
			c := new(Columns)
			c.tableColumnMap, err = GetTableColumns(db, driverName, tableName)
			nIndex, err := strconv.Atoi(sheetIndex)
			utils.Checkerr2(err, "")
			if nIndex >= len(xlFile.Sheets) {
				fmt.Printf("Sheet index should small then length of sheets.sheetIndex:%d, "+
					"len(xlFile.Sheets):%d\n", sheetIndex, len(xlFile.Sheets))
				return
			}
			sheet_ := xlFile.Sheets[nIndex]
			fmt.Printf("--------sheetIndex%s, %s----------\n", sheetIndex, sheet_.Name)
			var subResultValues []string
			var modifiedIds []int
			modifiedIds, subResultValues, err = FromExcelSheetWithResult(c, sheet_, db, dataStartRow, driverName,
				tableName, sheetIndex, debug, useNull4BlankColumn, resultFieldName)
			if err != nil && utils.Checkerr2(err, fmt.Sprintf("sheetIndex:%d", nIndex)) {
				msg := "error"
				if err != nil {
					msg += ":" + err.Error()
				}
				sign <- msg
			} else {
				count := 0
				if modifiedIds != nil {
					count = len(modifiedIds)
				}
				sign <- fmt.Sprintf("success:%d", count)
				resultChan <- ResultStruct{
					SheetIndex:  nIndex,
					ModifiedIds: modifiedIds,
					Results:     subResultValues,
					Err:         err,
				}
			}
		}(sheetIndex)
	}
	for sheetIndex := 0; sheetIndex < len(sheetSlice); sheetIndex++ {
		msg := <-sign
		result := <-resultChan
		resultValues = append(resultValues, result)
		fmt.Printf("sheetIndex:%d, msg:%s\n", sheetIndex, msg)
	}
	return
}

func FromExcelSheetWithResult(c *Columns, sheet *xlsx.Sheet, db *sql.DB, dataStartRow int, driverName, tableName string,
	sheetIndex string, debug bool, useNull4BlankColumn bool, resultFieldName string) (modifiedIds []int,
	resultValues []string, err error) {
	err = nil
	if sheet.Rows == nil {
		err = errors.New("该sheet没有数据")
		return
	}
	for titleRowIndex := 0; titleRowIndex <= dataStartRow-2; titleRowIndex++ {
		err = initColumns(c, sheet, titleRowIndex)
		if err == nil {
			break
		} else {
			c.sourceColumns = make([]StringValueInterface, 0)
			c.useColumns = make(map[int][]string, 0)
		}
	}

	utils.Checkerr2(err, "FromExcelSheetWithResult, initColumns error")

	rowsNum := len(sheet.Rows) //- dataStartRow + 2
	blankRowSeqCount := 0      //连续的空行行数
	blankRowLast := false      //上一行是空行
	blankRow := false          //本行是空行
OutFor:
	for rowIndex := dataStartRow - 1; rowIndex < rowsNum; rowIndex++ {
		dbRow := &DBRow{value: make(map[string]string), sql: "", ot: OtherTable{}}
		tmp := 0
		// 字段
		var insertIntoFieldNames []string     //INSERT INTO ()
		needConflictOnFieldSet := set.New()   //CONFLICT ON()
		distinctExcludedFieldSet := set.New() //where tbl.c3 is distinct from excluded.c3 or tbl.c4 is distinct from excluded.c4
		updatedFieldSet := set.New()          // DO UPDATE SET

		var values []string

		needConflictOnFieldsOther := ""
		updateSetSql := ""
		whereSql := ""
		id := ""
		returningFields := []string{"id"}
		var rows *sql.Rows
		var uniqTogetherMap = map[string]string{}
		var resultUniqSql *map[string]string
		if blankRow {
			if blankRowLast {
				blankRowSeqCount += 1
			} else {
				blankRowSeqCount = 1
			}
			blankRowLast = true
			if blankRowSeqCount >= 3 { //连续三行是空行 判断内容结束
				break
			}
		} else {
			blankRowLast = false
			blankRowSeqCount = 0
		}
		blankRow = true
		noWhere := false
	InnerFor:
		for key, columnFieldValues := range c.useColumns {
			if columnFieldValues == nil || len(sheet.Rows) <= 0 {
				fmt.Printf("c.useColumns:%#v\n", c.useColumns)
				continue
			}
			if len(sheet.Rows[rowIndex].Cells) <= 0 {
				continue OutFor
			}
			if key >= len(sheet.Rows[rowIndex].Cells) || sheet.Rows[rowIndex].Cells[key] == nil {
				dbRow.value[columnFieldValues[0]] = ""
			} else {
				dbRow.value[columnFieldValues[0]] = sheet.Rows[rowIndex].Cells[key].String()
			}
			if dbRow.value[columnFieldValues[0]] != "" {
				blankRow = false
			}
			//解析内容
			if columnFieldValues[0] == ":other" {
				dbRow.ot.value = strings.Split(dbRow.value[columnFieldValues[0]], "|")
				sqlOther := "SELECT * FROM " + utils.EscapeString(driverName, dbRow.ot.value[0])
				rows, err = db.Query(sqlOther + ";")
				utils.Checkerr2(err, sqlOther)
				dbRow.ot.columns, err = rows.Columns()
				rows.Close()
				utils.Checkerr2(err, sqlOther)

				for _, v := range columnFieldValues {
					if strings.Index(v, "unique=") >= 0 {
						needConflictOnFieldsOther = strings.Join(strings.Split(strings.TrimPrefix(v,
							"unique="), "+"), ",")
					}
				}
			} else {
				isGenerate := false
				ignoreBlank := false
				json := false
				if len(columnFieldValues) > 1 {
					switch columnFieldValues[1] {
					case "json": //如果这个字段的值是blank，忽略这个字段
						ignoreBlank = true
						json = true
					case "ignoreBlank": //如果这个字段的值是blank，忽略这个字段
						ignoreBlank = true
					case "update":
						distinctExcludedFieldSet.Add(columnFieldValues[0])
						updatedFieldSet.Add(columnFieldValues[0])
					case "generate":
						isGenerate = true
						if columnFieldValues[0] == "uuid" {
							dbRow.value[columnFieldValues[0]] = uuid.New().String()
							returningFields = append(returningFields, columnFieldValues[0])
						} else if columnFieldValues[0] == "short_uuid" {
							dbRow.value[columnFieldValues[0]] = shortuuid.New()
							returningFields = append(returningFields, columnFieldValues[0])
						} else {
							msg := fmt.Sprintf("columnFieldValues:%#v", columnFieldValues)
							//fmt.Println(msg)
							err = errors.New(msg)
							//sign <- "error"
							return
						}
					case "unique":
						if id != "" {
							continue
						}
						uniqueSql := "SELECT * FROM  " +
							utils.EscapeString(driverName, tableName) + "  WHERE  " + columnFieldValues[0] + "  = '" +
							utils.EscapeSpecificChar(dbRow.value[columnFieldValues[0]]) + "'"
						if dbRow.value[columnFieldValues[0]] == "" {
							fmt.Printf("[sheet"+sheetIndex+"-"+strconv.Itoa(rowIndex+1)+"/"+strconv.Itoa(rowsNum+1)+
								"] FromExcelSheet ignored:uniq field value is blank, uniqueSql:%s\n", uniqueSql)
							continue OutFor
						}
						//fmt.Printf("uniqueSql:%s\n", uniqueSql)
						resultUniqSql, _ = utils.FetchRow(db, uniqueSql)
						id = (*resultUniqSql)["id"]
						if id != "" {
							needConflictOnFieldSet.Add(columnFieldValues[0])
						}
					case "uniq_together":
						uniqTogetherMap[columnFieldValues[0]] = utils.EscapeSpecificChar(dbRow.value[columnFieldValues[0]])
						needConflictOnFieldSet.Add(columnFieldValues[0])
					case "password":
						tmpvalue := strings.Split(dbRow.value[columnFieldValues[0]], "|")
						if len(tmpvalue) == 2 {
							if []byte(tmpvalue[1])[0] == ':' {
								if _, ok := dbRow.value[string([]byte(tmpvalue[1])[1:])]; ok {
									dbRow.value[columnFieldValues[0]] = tmpvalue[0] +
										dbRow.value[string([]byte(tmpvalue[1])[1:])]
								} else {
									msg := "[" + strconv.Itoa(rowIndex+1) + "/" + strconv.Itoa(rowsNum+1) +
										"]密码盐" + string([]byte(tmpvalue[1])[1:]) + "字段不存在，自动跳过"
									//fmt.Println(msg)
									err = errors.New(msg)
									//sign <- "error"
									return
								}
							} else {
								dbRow.value[columnFieldValues[0]] += tmpvalue[1]
							}
						} else {
							dbRow.value[columnFieldValues[0]] = tmpvalue[0]
						}
						switch columnFieldValues[2] {
						case "md5":
							dbRow.value[columnFieldValues[0]] = string(md5.New().Sum([]byte(dbRow.value[columnFieldValues[0]])))
						case "bcrypt":
							pass, _ := bcrypt.GenerateFromPassword([]byte(dbRow.value[columnFieldValues[0]]), 13)
							dbRow.value[columnFieldValues[0]] = string(pass)
						}
					case "find":
						if strings.TrimSpace(dbRow.value[columnFieldValues[0]]) == "" {
							//空白列的处理
							if useNull4BlankColumn {
								distinctExcludedFieldSet.Add(columnFieldValues[0])
								updatedFieldSet.Add(columnFieldValues[0])
								dbRow.value[columnFieldValues[0]] = "NULL"
								break
							} else {
								// 跳过该列
								continue InnerFor
							}
						}
						sqlFind := "SELECT * FROM  " + utils.EscapeString(driverName, columnFieldValues[2]) +
							"  WHERE " + columnFieldValues[4] + " = '" +
							strings.TrimSpace(dbRow.value[columnFieldValues[0]]) + "'"
						result, _ := utils.FetchRow(db, sqlFind)
						if (*result)[columnFieldValues[3]] == "" {
							msg := "[sheet" + sheetIndex + "-" + strconv.Itoa(rowIndex+1) + "/" + strconv.Itoa(rowsNum+1) + "]表" +
								columnFieldValues[2] + "中没有找到" + columnFieldValues[4] + "为" +
								dbRow.value[columnFieldValues[0]] + "的数据，自动跳过"
							fmt.Println(msg)
							//有的数据不合法 跳过就可以 不必停止处理其它数据
							continue OutFor
						}
						distinctExcludedFieldSet.Add(columnFieldValues[0])
						updatedFieldSet.Add(columnFieldValues[0])
						dbRow.value[columnFieldValues[0]] = (*result)[columnFieldValues[3]]
					}

					switch columnFieldValues[len(columnFieldValues)-1] {
					case "ignoreBlank": //如果这个字段的值是blank，忽略这个字段
						ignoreBlank = true
					case "json": //如果这个字段的值是blank，忽略这个字段
						ignoreBlank = true
						json = true
					case "uniq_together":
						uniqTogetherMap[columnFieldValues[0]] = utils.EscapeSpecificChar(dbRow.value[columnFieldValues[0]])
						needConflictOnFieldSet.Add(columnFieldValues[0])
					}
				}
				var needUpdate bool
				dbRow.value[columnFieldValues[0]], needUpdate = ParseValue(dbRow.value[columnFieldValues[0]])

				if dbRow.value[columnFieldValues[0]] != "" || (!ignoreBlank && needUpdate) {
					noWhere = noWhere || (json && dbRow.value[columnFieldValues[0]] != "")
					insertIntoFieldNames = append(insertIntoFieldNames, columnFieldValues[0])
					value := strings.TrimSpace(dbRow.value[columnFieldValues[0]])
					values = append(values, utils.EscapeValuesString(driverName, value))
					if !isGenerate || needUpdate { //自动生成的不能放在update语句里面
						updatedFieldSet.Add(columnFieldValues[0])
					}
					if needUpdate {
						distinctExcludedFieldSet.Add(columnFieldValues[0])
					}
					if resultFieldName != "" && columnFieldValues[0] == resultFieldName {
						resultValues = append(resultValues, value)
					}
					tmp++
				}
			}
		}
		if blankRow { //空行不处理
			continue OutFor
		}
		var result *map[string]string
		uniqTogetherSql := ""
		if id == "" {
			if len(uniqTogetherMap) > 0 {
				uniqTogetherSql = generateUniqTogetherSql(driverName, tableName, uniqTogetherMap, false)
				result, err = utils.FetchRow(db, uniqTogetherSql)
				if err != nil { //sql语句中，使用0代替NULL 再试一次
					uniqTogetherSql = generateUniqTogetherSql(driverName, tableName, uniqTogetherMap, true)
					result, err = utils.FetchRow(db, uniqTogetherSql)
				}
				if err != nil {
					fmt.Printf("FetchRow err,uniqTogetherSql:%s, error:%s\n", uniqTogetherSql, err.Error())
				}
			}
		} else {
			result = resultUniqSql
		}
		if result != nil {
			for _, fieldReturning := range returningFields {
				if fieldReturning == "id" {
					id = (*result)["id"]
				} else { // 如果没有值，就需要update
					if (*result)[fieldReturning] == "" || (*result)[fieldReturning] == "NULL" {
						updatedFieldSet.Add(fieldReturning)
						distinctExcludedFieldSet.Add(fieldReturning)
						fmt.Printf("fieldReturning:%s, (*result)[fieldReturning]:%s, uniqTogetherSql:%s\n", fieldReturning, (*result)[fieldReturning], uniqTogetherSql)
					} else {
						updatedFieldSet.Remove(fieldReturning)
						distinctExcludedFieldSet.Remove(fieldReturning)
					}
				}
			}
		}
		needConflictOnFields := ""
		if needConflictOnFieldSet.Len() > 0 {
			needConflictOnFields = strings.Join(needConflictOnFieldSet.List(), ",")
		}
		insertSql, updateSetSql, whereSql := GetUpdateSql(driverName, tableName, insertIntoFieldNames, values,
			needConflictOnFields, updatedFieldSet, distinctExcludedFieldSet)
		if noWhere {
			whereSql = ""
		}
		dbRow.sql = insertSql
		useUpdate := false
		if needConflictOnFieldSet.Len() > 0 && updateSetSql != "" {
			dbRow.sql += " ON CONFLICT (" + strings.Join(needConflictOnFieldSet.List(), ",") + ") DO UPDATE SET " +
				updateSetSql + whereSql
			useUpdate = true
		}
		dbRow.sql += " RETURNING id"
		if debug {
			fmt.Printf("dbRow.sql:%s\n", dbRow.sql)
		}
		err = db.QueryRow(dbRow.sql + ";").Scan(&dbRow.insertID)
		if err != nil && useNull4BlankColumn &&
			strings.Contains(err.Error(), "invalid input syntax for integer") &&
			strings.Contains(err.Error(), "NULL") {
			//空白列的处理 NULL不行就用0代替
			dbRow.sql = strings.ReplaceAll(dbRow.sql, "NULL", "0")
			err = db.QueryRow(dbRow.sql + ";").Scan(&dbRow.insertID)
		}
		if err != nil || dbRow.insertID == 0 {
			if !useUpdate || strings.Index(err.Error(), "no rows in result set") < 0 {
				fmt.Printf("err:%s\n", err.Error())
				fmt.Printf("dbRow.sql:%s\n", dbRow.sql)
			} else if strings.Index(err.Error(), "no rows in result set") >= 0 {
				err = nil
			}
		}
		idOfMainRecord := int(dbRow.insertID)
		ignored := idOfMainRecord == 0
		if ignored {
			fmt.Printf("ignored,sql:%s\n", dbRow.sql)
			if id != "" {
				idOfMainRecord, _ = strconv.Atoi(id)
			}
		} else if debug {
			fmt.Printf("debug,sql:%s\n", dbRow.sql)
		}
		if idOfMainRecord == 0 {
			msg := "id is 0"
			if debug {
				err = errors.New(msg)
				return
			} else {
				fmt.Printf("err:%s\n", msg)
			}
		}
		utils.Checkerr2(err, dbRow.sql)
		if idOfMainRecord > 0 && err == nil {
			//更新id自增长值导入数据成功
			IncreaseTableIdSeq(db, tableName)
			if !ignored {
				modifiedIds = append(modifiedIds, idOfMainRecord)
			}
		}
		//执行附表操作
		operateOnOtherTable(driverName, db, dbRow, idOfMainRecord, needConflictOnFieldsOther, debug)

		fmt.Println("[sheet" + sheetIndex + "-" + strconv.Itoa(rowIndex+1) + "/" + strconv.Itoa(rowsNum+1) + "]导入数据成功")
	}
	return
}

func initColumns(c *Columns, sheet *xlsx.Sheet, titleRowIndex int) (err error) {
	for _, cell := range sheet.Rows[titleRowIndex].Cells {
		c.sourceColumns = append(c.sourceColumns, cell)
	}
	err = c.ParseColumns()
	return
}
